﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Xml;

namespace Groundwork.Utility.Data
{

    public class DataHelper
    {

        /*
         ERROR      : Could not load type 'ADODB.FieldsToInternalFieldsMarshaler
         WORKAROUND : project had adodb.dll as an Embeded Interop Type. change it to non-embeded and set "Copy Local" = True
         */

        /// <summary>
        /// Returns a list of objects converted from the inbound ADODB.Recordset
        /// </summary>
        /// <param name="BOL">ADODB.Recordset from the Bol</param>
        /// <returns>a list of dynamic objects</returns>
        [CLSCompliant(false)]
        public static Collection<dynamic> AsDynaList(ADODB.Recordset recordset)
        {
            Collection<dynamic> rs = new Collection<dynamic>();
            while (!recordset.EOF)
            {
                rs.Add(AsDynaSingle(recordset));
                recordset.MoveNext();
            }
            return rs;
        }

        /// <summary>
        /// Returns a single instance of a dynamic object based on the input ADODB.Recordset
        /// </summary>
        /// <param name="BOL">ADODB.Recordset from the bol</param>
        /// <returns>a single dynamic object representation of the recordset</returns>
        [CLSCompliant(false)]
        public static dynamic AsDynaSingle(ADODB.Recordset recordset)
        {
            var test = new ExpandoObject() as IDictionary<string, object>;
            foreach (ADODB.Field field in recordset.Fields)
            {
                test.Add(SafeName(field.Name), DBFieldValue<string>(field).Trim());
            }
            return test;
        }

        /// <summary>
        /// basic regex to allow us to search for punctuation
        /// </summary>
        private static Regex regExPunctuation = new Regex("(\\s*)(\\p{P}*)", RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled | RegexOptions.IgnoreCase);

        /// <summary>
        /// removes punctuation from an xml node name
        /// </summary>
        /// <param name="inputValue">node name</param>
        /// <returns>clean node name</returns>
        public static string SafeName(string inputValue)
        {
            return regExPunctuation.Replace(inputValue, string.Empty);
        }

        [CLSCompliant(false)]
        public static XmlDocument AsXml(string rootElement, ADODB.Recordset recordset)
        {
            XmlDocument _xml = new XmlDocument();
            _xml.LoadXml(rootElement);
            while (!recordset.EOF)
            {
                XmlNode _row = _xml.CreateElement("row");
                foreach (ADODB.Field field in recordset.Fields)
                {
                    XmlElement _fieldName = _xml.CreateElement(SafeName(field.Name));
                    _fieldName.InnerText = DBFieldValue<string>(field).Trim();
                    //  _fieldName.SetAttribute("type", field.Type.ToString());
                    _row.AppendChild(_fieldName);
                }
                _xml.DocumentElement.AppendChild(_row);
                recordset.MoveNext();
            }

            return _xml;
        }

        [CLSCompliant(false)]
        public static XmlDocument AsXml(string rootElement, List<string> columns, ADODB.Recordset bol)
        {
            XmlDocument _xml = new XmlDocument();
            _xml.LoadXml(rootElement);
            while (!bol.EOF)
            {
                XmlNode _row = _xml.CreateElement("row");
                foreach (ADODB.Field field in bol.Fields)
                {
                    string fieldName = SafeName(field.Name);
                    if (columns.Contains(fieldName))
                    {
                        XmlElement _fieldName = _xml.CreateElement(fieldName);
                        _fieldName.InnerText = DBFieldValue<string>(field);
                        // _fieldName.SetAttribute("type", field.Type.ToString());
                        _row.AppendChild(_fieldName);
                    }
                }
                _xml.DocumentElement.AppendChild(_row);
                bol.MoveNext();
            }

            return _xml;
        }

        [CLSCompliant(false)]
        public static XmlDocument AsXml(string rootElement, List<string> columns, ADODB.Recordset bol, string keyField)
        {
            XmlDocument _xml = new XmlDocument();
            _xml.LoadXml(rootElement);
            while (!bol.EOF)
            {
                XmlNode _row = _xml.CreateElement("row");
                foreach (ADODB.Field field in bol.Fields)
                {
                    string fieldName = SafeName(field.Name);
                    if (columns.Contains(fieldName))
                    {
                        XmlElement _fieldName = _xml.CreateElement(fieldName);
                        _fieldName.InnerText = DBFieldValue<string>(field);
                        if (fieldName.ToLower() == keyField.ToLower())
                        {
                            _fieldName.SetAttribute("key", "true");
                        }
                        //_fieldName.SetAttribute("type", field.Type.ToString());
                        _row.AppendChild(_fieldName);
                    }
                }
                _xml.DocumentElement.AppendChild(_row);
                bol.MoveNext();
            }

            return _xml;
        }

        /// <summary>
        /// Maps a given "Flat" model onto an adodb.recordset. The mapping is based on the recordset field names matching the model property names
        /// </summary>
        /// <typeparam name="T">Model to map to </typeparam>
        /// <param name="BOL">ADODB.Recordset generated by the BOL</param>
        /// <returns>A list of models which map to the recordset</returns>
        [CLSCompliant(false)]
        public static Collection<T> MapRecordsetToModel<T>(ADODB.Recordset bol)
        {
            return MapToModel<T>(bol);
        }

        /// <summary>
        /// Maps a Flat recordset to a flat model on a 1:1 mapping of fieldname to property name
        /// </summary>
        /// <typeparam name="T">type of mapp</typeparam>
        /// <param name="BOL">BOL recordset</param>
        /// <param name="template">type template to map</param>
        /// <returns>a list of types mapped</returns>
        private static Collection<T> MapToModel<T>(ADODB.Recordset bol)
        {
            Collection<T> _tmp = new Collection<T>();
            while (!bol.EOF)
            {
                T instance = (T)Activator.CreateInstance(typeof(T));
                foreach (ADODB.Field field in bol.Fields)
                {
                    string FieldName = SafeName(field.Name);
                    PropertyInfo info = instance.GetType().GetProperty(FieldName);
                    if (info != null)
                    {
                        string FieldValue = DBFieldValue<string>(field);
                        info.SetValue(instance, FieldValue, null);
                    }
                }
                _tmp.Add(instance);
                bol.MoveNext();
            }
            return _tmp;
        }

        /// <summary>
        /// Checks the recordset field for null values, if a null is not found the recordset value is converted to the 
        /// requested Type
        /// </summary>
        /// <typeparam name="T">Type to convert to</typeparam>
        /// <param name="obj">ADODB.REcordset object</param>
        /// <param name="property">field to examine</param>
        /// <returns>Field value converted to Type</returns>
        [CLSCompliant(false)]
        public static T DBFieldValue<T>(Recordset obj, string property)
        {
            if (obj!=null)
            {
                try
                {
                    if (obj.Fields[property] != null)
                    {
                        return (T)Convert.ChangeType(obj.Fields[property].Value, typeof(T), Thread.CurrentThread.CurrentCulture);
                    }
                }
                catch (FieldAccessException ex)
                {
                    throw ex;
                }
            }
            return default(T);
        }

        [CLSCompliant(false)]
        public static T DBFieldValue<T>(Recordset obj, string property, T defaultValue)
        {
            if (obj!=null)
            {
                try
                {
                    if (obj.Fields[property]!=null)
                    {
                        return (T)Convert.ChangeType(obj.Fields[property].Value, typeof(T), Thread.CurrentThread.CurrentCulture);
                    }
                }
                catch { }
            }
            return (T)Convert.ChangeType(defaultValue, typeof(T));
        }

        /// <summary>
        /// Check the input field type for null values
        /// </summary>
        /// <typeparam name="T">Type to convert to</typeparam>
        /// <param name="obj">ADODB.REcordset object</param>
        /// <returns>Field value converted to Type</returns>
        [CLSCompliant(false)]
        public static T DBFieldValue<T>(Field obj)
        {
            if (obj!=null)
            {
                if (obj.Name != "Stamp")
                {
                    return (T)Convert.ChangeType(obj.Value, typeof(T), Thread.CurrentThread.CurrentCulture);
                }
            }
            return default(T);
        }
    }
}
